﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Runtime.Serialization;
using System.Windows.Forms;
using System.Xml;
using ApplicationLibrary;
using ApplicationLibrary.DBManager;
using ApplicationLibrary.SqlConnector;
using Vintner.Masters.ModuleData;

namespace Vintner.Services.Forms
{
    public partial class DadoMigrator : ApplicationLibrary.DBManager.BatchForm
    {
        private string connString = string.Empty;
        private DRDescriptions drCategory = new DRDescriptions(DescriptionType.E_Category, true);
        private DRTaxCode drTaxCode = new DRTaxCode(true);
        private Dictionary<int, string> IDArt = new Dictionary<int, string>();
        private Dictionary<int, string> IDFatt = new Dictionary<int, string>();
        private Dictionary<int, string> IDCli = new Dictionary<int, string>();
        private ProviderType provider;

        public DadoMigrator()
            : base("DadoMigrator")
        {
            InitializeComponent();

            //connString = "Data Source=USR-ROGLEDIMAU1;Initial Catalog='Dado';Integrated Security='SSPI';Connect Timeout=30; Pooling= false";
            connString = "Data Source=SALA\\SQLEXPRESS;Initial Catalog='Dado';Integrated Security='SSPI';Connect Timeout=30; Pooling= false";
            DeSerialize<Dictionary<int, string>>("IDArt.xml", ref IDArt);
            DeSerialize<Dictionary<int, string>>("IDFatt.xml", ref IDFatt);
            DeSerialize<Dictionary<int, string>>("IDCli.xml", ref IDCli);
        }

        protected override void OnAttachData()
        {
            base.OnAttachData();
        }

        protected override void OnBatchExecute()
        {
            SqlABConnection scon = new SqlABConnection(provider, connString);
            scon.Open();
            if (ckbCust.Checked) MigraClienti(scon);
            if (ckbItem.Checked) MigraArticoli(scon);
            if (ckbPrice.Checked) MigraPrezzi(scon);
            if (ckbItemPrice.Checked) MigraAbbinamentoPrezzi(scon);
            if (ckbInvHeader.Checked) MigraTestaFattura(scon);
            if (ckbInvDet.Checked) MigraRigheFattura(scon);
            if (scon.State == ConnectionState.Open)
                scon.Close();
        }

        private void btnSelect_Click(object sender, System.EventArgs e)
        {
            ConnectionForm myConnectionForm = new ConnectionForm();

            if (myConnectionForm.ShowDialog() == DialogResult.OK)
            {
                connString = SqlManagerString.GetConnectionString(myConnectionForm.DataBase_Provider,
                                    myConnectionForm.DataBase_Host, myConnectionForm.DataBase_Username,
                                    myConnectionForm.DataBase_Password, myConnectionForm.DataBase_Name,
                                    myConnectionForm.DataBase_Authentication == AuthenticationMode.Windows,
                                    myConnectionForm.DataBase_NewDatabase);

                provider = myConnectionForm.DataBase_Provider;
                txtDatabase.Text = myConnectionForm.DataBase_Name;
                txtServer.Text = myConnectionForm.DataBase_Host;
            }
        }

        private void CreateIvaAndCategories(string cat, string iva)
        {
            if (!drCategory.Find(cat))
            {
                DataRow dr = drCategory.AddRecord();
                DataRowValues.SetValue<DescriptionType>(dr, VI_Descriptions.Type, DescriptionType.E_Category);
                DataRowValues.SetValue<string>(dr, VI_Descriptions.Code, cat);
                DataRowValues.SetValue<string>(dr, VI_Descriptions.Description, cat);
                drCategory.Update();
            }

            if (!drTaxCode.Find(iva))
            {
                DataRow dr = drTaxCode.AddRecord();
                DataRowValues.SetValue<string>(dr, VI_TaxCode.Code, iva);
                DataRowValues.SetValue<string>(dr, VI_TaxCode.Description, iva);
                DataRowValues.SetValue<double>(dr, VI_TaxCode.Percent, double.Parse(iva));
                drTaxCode.Update();
            }
        }

        private string DecodeCondPag(string condpag)
        {
            if (condpag == "Contrassegno")
                return "CONTRAS";
            else if (condpag == "Vs Bonifico Bancario a 60 gg. fatt.")
                return "BON60GG";
            else if (condpag == "FINE  MESE")
                return "FINEMESE";
            else if (condpag == "CONSEGNA")
                return "CONSEGNA";
            else if (condpag == "30 gg. DATA FATTURA")
                return "30GGDF";
            else if (condpag == "60 gg. DATA FATTURA")
                return "60GGDF";
            else if (condpag == "Vs. Bonifico Bancario a 30 gg. fatt")
                return "BONBAN30";
            else if (condpag == "spedizione Vs. Assegno bancario")
                return "ASSBAN";

            return "";
        }

        private string DecodeItem(string code)
        {
            string pref = string.Empty;
            int val = 0;
            string suff = string.Empty;
            int sufStart = 0;
            int numpos = 0;

            if ("01234567890".IndexOf(code.Substring(2, 1)) >= 0)
            {
                pref = code.Substring(0, 2);
                numpos = 2;

                //System.Diagnostics.Debug.WriteLine("XX00 " + code);
            }
            else
            {
                if (code.Substring(2, 1) == " " && "01234567890".IndexOf(code.Substring(3, 1)) >= 0)
                {
                    pref = code.Substring(0, 2);
                    numpos = 3;

                    //System.Diagnostics.Debug.WriteLine("XX 00 " + code);
                }
                else
                {
                    pref = code.Substring(0, 3);
                    numpos = 4;

                    //System.Diagnostics.Debug.WriteLine("XXX 00 " + code);
                }
            }

            //if (code.StartsWith("MEZ"))
            //    System.Diagnostics.Debug.WriteLine("qua");

            if (numpos + 2 < code.Length && "01234567890".IndexOf(code.Substring(numpos + 2, 1)) >= 0)
            {
                val = int.Parse(code.Substring(numpos, 3));

                //System.Diagnostics.Debug.WriteLine(code.Substring(numpos, 3) + " " + code);
                sufStart = numpos + 3;
            }
            else
            {
                val = int.Parse(code.Substring(numpos, 2));
                sufStart = numpos + 2;

                //System.Diagnostics.Debug.WriteLine(code.Substring(numpos, 2) + " " + code);
            }

            if (code.IndexOf("IA") >= 0)
                suff = "BIA";
            else
                if (code.IndexOf("DAM") >= 0)
                    suff = "DAM";
                else
                    if (code.IndexOf("ONE") >= 0)
                        suff = "BNE";
                    else
                        if (code.IndexOf("CON") >= 0)
                            suff = "CFZ";
                        else
                            if (code.IndexOf("TTE") >= 0)
                                suff = "BTTE";
                            else
                                if (code.IndexOf("FUS") >= 0)
                                    suff = "FSTO";
                                else
                                    if (code.IndexOf("INA") >= 0)
                                        suff = "LTNA";
                                    else
                                        if (code.IndexOf("LAT") >= 0)
                                            suff = "LTTA";
                                        else
                                        {
                                            if (sufStart < code.Length)
                                                suff = code.Substring(sufStart, code.Length - sufStart);
                                            else
                                                suff = "";
                                        }
            return string.Format("{0,-3}{1:000}{2}", pref, val, suff);
        }

        private string DecodeMezzo(string mezzo)
        {
            return (mezzo == "Cedente")
                    ? "CEDENTE"
                    : "CESSION";
        }

        private void FillMessageList(List<MessageData> errorlist)
        {
            foreach (MessageData msg in errorlist)
            {
                int row = dgwMessages.Rows.Add();
                dgwMessages.Rows[row].Cells["colKey"].Value = msg.key;
                dgwMessages.Rows[row].Cells["colApplication"].Value = msg.document;
                dgwMessages.Rows[row].Cells["colHeader"].Value = msg.caption;
                dgwMessages.Rows[row].Cells["colText"].Value = msg.text;
            }

            errorlist.Clear();
        }

        private bool MigraAbbinamentoPrezzi(SqlABConnection scon)
        {
            Masters.Library.CustomersCreator custCreator = (Masters.Library.CustomersCreator)GlobalInfo.OpenDataManager("Vintner.Vintner.Masters.Library.CustomersCreator");
            SqlABCommand sc = new SqlABCommand(provider, scon);
            QueryBuilder qb = new QueryBuilder();
            qb.AddSelectAll("R_Abb_Prz");
            qb.AddFrom("R_Abb_Prz");
            qb.AddOrder("RAP_CLI");

            sc.CommandText = qb.Query;
            SqlABDataReader dr = sc.ExecuteReader();

            int oldCode = 0;
            string codArt = string.Empty;
            while (dr.Read())
            {
                Description = dr["RAP_Des"].ToString();

                if (!IDArt.ContainsKey((int)dr["RAP_Art"]))
                    continue;

                codArt = IDArt[(int)dr["RAP_Art"]];

                int newCode = (int)dr["RAP_CLI"];
                if (oldCode != newCode)
                {
                    if (oldCode != 0)
                        custCreator.Save();

                    if (!custCreator.Find(newCode.ToString().PadLeft(4)))
                        continue;

                    if (!custCreator.Edit())
                        continue;

                    oldCode = newCode;
                }

                try
                {
                    custCreator.AddPrice(codArt, dr.GetString("RAP_Cat"));
                }
                catch (Exception exc)
                {
                }
            }

            custCreator.Save();
            dr.Close();

            return true;
        }

        private bool MigraArticoli(SqlABConnection scon)
        {
            Items.Library.ItemsCreator itemCreator = (Items.Library.ItemsCreator)GlobalInfo.OpenDataManager("Vintner.Vintner.Items.Library.ItemsCreator");
            SqlABCommand sc = new SqlABCommand(provider, scon);
            QueryBuilder qb = new QueryBuilder();
            qb.AddSelectAll("Magazzino");
            qb.AddFrom("Magazzino");

            sc.CommandText = qb.Query;

            SqlABDataReader dr = sc.ExecuteReader();
            while (dr.Read())
            {
                Description = dr["M_Desc"].ToString();

                if (itemCreator.AddNew())
                {
                    string code = DecodeItem(dr.GetString("M_Cod"));
                    IDArt.Add((int)dr["M_ID"], code);
                    itemCreator.SetData1(code, dr.GetString("M_Desc"), dr.GetDouble("M_Qt"));

                    FillMessageList(itemCreator.ErrorMessageList);

                    itemCreator.Save();
                }
            }
            dr.Close();

            Serialize<Dictionary<int, string>>("IDArt.xml", IDArt);
            return true;
        }

        private bool MigraClienti(SqlABConnection scon)
        {
            Masters.Library.CustomersCreator custCreator = (Masters.Library.CustomersCreator)GlobalInfo.OpenDataManager("Vintner.Vintner.Masters.Library.CustomersCreator");

            SqlABCommand sc = new SqlABCommand(provider, scon);
            QueryBuilder qb = new QueryBuilder();
            qb.AddSelectAll("Clienti");
            qb.AddFrom("Clienti");

            sc.CommandText = qb.Query;

            SqlABDataReader dr = sc.ExecuteReader();
            int maxID = 0;
            while (dr.Read())
            {
                Description = dr["C_RagS"].ToString();
                if (dr["C_ID"].ToString() == "175")
                    MessageBox.Show("Eccomi");
                if (custCreator.AddNew())
                {
                    custCreator.SetData1(dr["C_ID"].ToString(), dr["C_RagS"].ToString());
                    custCreator.SetData2(dr["C_Ind"].ToString(), dr["C_Cap"].ToString(),
                                         dr["C_Citta"].ToString(), dr["C_Pv"].ToString());
                    custCreator.SetData3(dr["C_Tel1"].ToString(), dr["C_Rif1"].ToString(),
                                         dr["C_Tel2"].ToString(), dr["C_Rif2"].ToString(),
                                         dr["C_Fax"].ToString());
                    custCreator.SetData4(dr["C_Piva"].ToString(), dr["C_CFisc"].ToString(),
                                         dr["C_Dest"].ToString());

                    custCreator.Save();
                }
                FillMessageList(custCreator.ErrorMessageList);

                if (int.Parse(dr["C_ID"].ToString()) > maxID)
                    maxID = int.Parse(dr["C_ID"].ToString());
            }
            dr.Close();

            ApplicationLibrary.CounterManager.CounterManager cm = new ApplicationLibrary.CounterManager.CounterManager(GlobalInfo.GetCounter(Properties.Resources.CN_Customers), GlobalInfo.CurrentDate.Year, "");
            cm.SetValue(maxID);

            custCreator.Close();
            return true;
        }

        private bool MigraPrezzi(SqlABConnection scon)
        {
            Items.Library.ItemsCreator itemCreator = (Items.Library.ItemsCreator)GlobalInfo.OpenDataManager("Vintner.Vintner.Items.Library.ItemsCreator");
            SqlABCommand sc = new SqlABCommand(provider, scon);
            QueryBuilder qb = new QueryBuilder();
            qb.AddSelectAll("Prezzi");
            qb.AddFrom("Prezzi");
            qb.AddCompare("mp_Prezzo", "NULL", "IS NOT");

            sc.CommandText = qb.Query;
            SqlABDataReader dr = sc.ExecuteReader();

            string oldCode = string.Empty;
            while (dr.Read())
            {
                CreateIvaAndCategories(dr["MP_Cat"].ToString(), dr["MP_Iva"].ToString());
                if (!IDArt.ContainsKey((int)dr["MP_Art"]))
                    continue;

                string newCode = IDArt[(int)dr["MP_Art"]];
                Description = newCode;

                ;
                if (oldCode != newCode)
                {
                    if (oldCode != string.Empty)
                        itemCreator.Save();

                    if (!itemCreator.Find(newCode))
                        continue;

                    if (!itemCreator.Edit())
                        continue;

                    oldCode = newCode;
                }

                try
                {
                    itemCreator.AddPrice(dr.GetString("MP_Cat"), dr.GetDouble("mp_Prezzo"), dr.GetInt16("MP_Iva").ToString());
                }
                catch (Exception exc)
                {
                }
                FillMessageList(itemCreator.ErrorMessageList);
            }

            itemCreator.Save();
            dr.Close();

            return true;
        }

        private bool MigraTestaFattura(SqlABConnection scon)
        {
            Sales.Library.SaleInvoicesCreator saleCreator = (Sales.Library.SaleInvoicesCreator)GlobalInfo.OpenDataManager("Vintner.Vintner.Sales.Library.SaleInvoicesCreator");
            SqlABCommand sc = new SqlABCommand(provider, scon);
            QueryBuilder qb = new QueryBuilder();
            qb.AddSelectAll("T_Fattura");
            qb.AddFrom("T_Fattura");
            qb.AddOrder("TF_Data");
            qb.AddOrder("TF_Num");

            sc.CommandText = qb.Query;
            SqlABDataReader dr = sc.ExecuteReader();

            while (dr.Read())
            {
                Description = string.Concat(dr["TF_Num"].ToString(), " ", dr["TF_Data"].ToString());
                if (saleCreator.AddNew())
                {
                    saleCreator.SetNumberAndData(int.Parse(dr["TF_Num"].ToString()), DateTime.Parse(dr["TF_Data"].ToString()));
                    saleCreator.SetCustomerData(dr["TF_Cli"].ToString(), dr["TF_Dest"].ToString());
                    saleCreator.SetAppereance(dr.GetInt16("TF_Colli"), dr.GetDouble("TF_Litri"), dr.GetBoolean("TF_Cartoni"),
                        dr.GetBoolean("TF_Cestelli"), dr.GetBoolean("TF_Damigiane"), dr.GetBoolean("TF_Fusti"));
                    string condpag = DecodeCondPag(dr.GetString("TF_CondPag"));
                    string mezzo = DecodeMezzo(dr.GetString("TF_Mezzo"));
                    saleCreator.SetTransportData(dr.GetDateTime("TF_DataTras"), dr.GetString("TF_OraTras"), condpag, mezzo);

                    IDFatt.Add((int)dr["TF_ID"], saleCreator.DocumentNo);
                }

                FillMessageList(saleCreator.ErrorMessageList);
                saleCreator.Save();
            }

            return true;
        }

        private bool MigraRigheFattura(SqlABConnection scon)
        {
            Sales.Library.SaleInvoicesCreator saleCreator = (Sales.Library.SaleInvoicesCreator)GlobalInfo.OpenDataManager("Vintner.Vintner.Sales.Library.SaleInvoicesCreator");
            SqlABCommand sc = new SqlABCommand(provider, scon);
            QueryBuilder qb = new QueryBuilder();
            qb.AddSelectAll("R_Fattura");
            qb.AddFrom("R_Fattura");
            qb.AddCompare("RF_Articolo", "0", "<>");
            qb.AddOrder("RF_Fatt");

            sc.CommandText = qb.Query;
            SqlABDataReader dr = sc.ExecuteReader();

            string oldCode = string.Empty;
            while (dr.Read())
            {
                if (!IDFatt.ContainsKey((int)dr["RF_Fatt"]))
                    continue;
                string newCode = IDFatt[(int)dr["RF_Fatt"]];
                Description = newCode;
                if (UserStopBatch())
                    return false;

                if (oldCode != newCode)
                {
                    if (oldCode != string.Empty)
                        saleCreator.Save();

                    if (!saleCreator.Find(newCode))
                        continue;

                    if (!saleCreator.Edit())
                        continue;
                    saleCreator.ClearRows();

                    oldCode = newCode;
                }

                try
                {
                    if (!IDArt.ContainsKey((int)dr["RF_Articolo"]))
                        continue;

                    saleCreator.AddRow(IDArt[(int)dr["RF_Articolo"]], dr.GetString("RF_Desc"), dr.GetDouble("RF_Quant"), dr.GetInt16("RF_Iva").ToString(),
                                       dr.GetDouble("RF_Litri"), dr.GetDouble("rf_Prezzo"), dr.GetString("rf_lotto"));
                }
                catch (Exception exc)
                {
                }
                FillMessageList(saleCreator.ErrorMessageList);
            }
            saleCreator.Save();
            return true;
        }

        private void Serialize<T>(string filename, T list)
        {
            DataContractSerializer ser =
                new DataContractSerializer(typeof(T));

            using (FileStream fs = new FileStream(filename, FileMode.OpenOrCreate))
            {
                ser.WriteObject(fs, list);
                fs.Close();
            }
        }

        private void DeSerialize<T>(string filename, ref T list)
        {
            if (!File.Exists(filename))
                return;

            FileStream fs = new FileStream(filename, FileMode.Open);
            XmlDictionaryReader reader = XmlDictionaryReader.CreateTextReader(fs, new XmlDictionaryReaderQuotas());
            DataContractSerializer ser = new DataContractSerializer(typeof(T));

            // Deserialize the data and read it from the instance.
            list = (T)ser.ReadObject(reader, true);
            reader.Close();
            fs.Close();
        }
    }
}